﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MDL;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class DAL_VipGrade
    {
        /// <summary>
        /// 查询会员等级信息
        /// </summary>
        /// <returns>会员等级信息</returns>
        public static List<VipGrade> SelectVipGrade()
        {
            string sql = string.Format(@"select * from VipGrade where 1=1");
            List<SqlParameter> sprs = new List<SqlParameter>();
            //if (!string.IsNullOrEmpty(vi.VGName))
            //{
            //    sql += " and VGName=@name";
            //    list.Add(new SqlParameter("@name", vi.VGName));
            //}           
            List<VipGrade> list = new List<VipGrade>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                while (dr.Read())
                {
                    VipGrade v = new VipGrade();
                    v.VGID = Convert.ToInt32(dr[0]);
                    v.VGName = dr[1].ToString();
                    v.VGDiscount = Convert.ToDouble(dr[2]);
                    list.Add(v);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询是否已存在此会员名称
        /// </summary>
        /// <param name="name">要查的会员名称</param>
        /// <returns>是否有</returns>
        public static List<VipGrade> SelectVipName(string name)
        {
            string sql = "select * from VIPGrade where VGName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name",name)};
            List<VipGrade> list = new List<VipGrade>();
            using (SqlDataReader dr = DBHelper.白道(sql,sprs,CommandType.Text))
            {
                while (dr.Read())
                {
                    VipGrade v = new VipGrade();
                    v.VGID = Convert.ToInt32(dr[0]);
                    list.Add(v);
                }
            }
            return list;
        }
        /// <summary>
        /// 添加会员等级
        /// </summary>
        /// <param name="name">会员名称</param>
        /// <param name="zk">会员折扣</param>
        /// <returns>受影响的行数</returns>
        public static int AddVipName(string name,string zk)
        {
            string sql = "insert into VIPGrade values(@name,@zk)";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@zk",zk),
                                  };
            return DBHelper.增删改(sql,sprs,CommandType.Text);
        }
        /// <summary>
        /// 修改会员等级
        /// </summary>
        /// <param name="name">会员名称</param>
        /// <param name="zk">会员折扣</param>
        /// <param name="id">根据会员ID</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateVipName(string name, string zk,int id)
        {
            string sql = "update VIPGrade set VGName = @name,VGDiscount=@zk where VGID = @id";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@zk",zk),
                                  new SqlParameter("@id",id)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 删除会员等级
        /// </summary>
        /// <param name="id">根据会员ID</param>
        /// <returns>受影响的行数</returns>
        public static int DeleteVipName(int id)
        {
            string sql = "delete from VIPGrade where VGID = @id";
            SqlParameter[] sprs = { new SqlParameter("@id", id) };
            return DBHelper.增删改(sql, sprs, CommandType.Text);

        }
        /// <summary>
        /// 下拉绑定会员等级信息
        /// </summary>
        /// <returns>会员等级信息</returns>
        public static List<VipGrade> SelectVip()
        {
            string sql = "select * from VIPGrade";
            List<VipGrade> list = new List<VipGrade>();
            using (SqlDataReader dr = DBHelper.白道(sql,null,CommandType.Text))
            {
                while (dr.Read())
                {
                    VipGrade v = new VipGrade();
                    v.VGID = Convert.ToInt32(dr[0]);
                    v.VGName = dr[1].ToString();
                    list.Add(v);
                }
            }
            return list;
        }
    }
}
